import gzip, MySQLdb

conn = MySQLdb.connect(host="mysql.stalidis.com", user="ict4growth", passwd="stanford", db="ict4growth")
conn.autocommit(False)

writer = conn.cursor()
#reader = conn.cursor()

#writer.execute('''CREATE TABLE BookIDs (ProductID varchar(20))''')

orfile = gzip.open("categories.txt.gz",'r')

pr_id = buf = "start"
category = set()

rows = 0
items = 0
books = 0
list_of_books = []

while not buf.startswith("B0007DQS8S"):	#skip to next item
	buf = orfile.readline()
	rows += 1

while buf != "":			#while not eof
	buf = orfile.readline()
	rows += 1
	if not buf.startswith(" "):
		items += 1
		if "Books" in category:
			books += 1
#			writer.execute("INSERT INTO BookDescriptions VALUES (%s,%s)",(pr_id,"NULL"))
			list_of_books.append((pr_id,"NULL"))
		pr_id = buf.strip()
		category = set()
	else:
		category.add(buf.strip().split(",")[0].strip())
	if len(list_of_books)%100 == 0:
		writer.executemany("INSERT INTO BookDescriptions VALUES (%s,%s)",list_of_books)
		conn.commit()
		list_of_books = []
	print "rows: ", rows, " ,items: ", items, " ,books: ", books

orfile.close()
conn.commit()
conn.close()

"""
			#print pr_id, list(category)
#			t = reader.execute("SELECT Description FROM Descriptions WHERE ProductID = %s", (pr_id,))
#			if t != 0L :
#				temp = reader.fetchone()[0]
#			else:
#				temp = "NULL"
"""
pass
